<?php
if (! defined ( 'BASEPATH' ))
	exit ( 'No direct script access allowed' );
class User_model extends CI_Model {
	
	function __construct() {
		parent::__construct ();
		self::is_login ();
	}
	
	function arrays($key = '') {
		$data = array ('cardType' => array ('1' => '身份证' ), 'sex' => array ('1' => '男', '2' => '女' ), 'statusArr' => array ('0' => "<span>未申请</span>", '1' => "<span style='color:green;'>已通过</span>", '2' => "<span style='color:red;'>待审核</span>" ), 'userFstatus' => array ('1' => "开通", '3' => "注销", '4' => "冻结" ), 'userLock' => array ('0' => "正常", '1' => "黑名单", '2' => "锁定" ), 'userType' => array ('2' => "普通客户", '3' => "客服" ),
		 'invite_layer' => array('1'=>0.04,'2'=>0.006,'3'=>0.004) );
		if ($key != '' && isset ( $data [$key] )) {
			return $data [$key];
		} else {
			return $data;
		}
	}
	
	function is_login() {
		$user = $this->session->userdata ( 'rsh_user' );
		//判断session是否存在
		if (isset ( $user ) && $user ['user_id'] > 0) {
			return $user;
		} else {
			//根据cookie判断用户是否选择保存登陆状态
			//cookie  key: rsh_online   pwd: rsh_login_20130826
			$ck_str = isset ( $_COOKIE ["zhsjr_online"] ) ? $_COOKIE ["zhsjr_online"] : '';
			
			if ($ck_str != '') {
				$this->load->library ( 'func' );
				//解密
				$tmp_u_str = $this->func->authcode ( $ck_str, 'DECODE', 'zhsjr_login' );
				$tmp_u_arr = explode ( ',', $tmp_u_str );
				if (count ( $tmp_u_arr ) == 6) {
					//$tmp_u_str: user_id,username,email,type_id,fstatus
					$tmp_u_id = $tmp_u_arr [0];
					$u_ret = self::get_by_uid ( $tmp_u_id );
					if ($u_ret) {
						if ($u_ret ['username'] == $tmp_u_arr [1] && $u_ret ['phone'] == $tmp_u_arr [2] && $u_ret ['email'] == $tmp_u_arr [3] && $u_ret ['type_id'] == $tmp_u_arr [4] && $u_ret ['fstatus'] == $tmp_u_arr [5]) {
							$user = array ('user_id' => $tmp_u_arr [0], 'username' => $tmp_u_arr [1], 'phone' => $tmp_u_arr [2], 'email' => $tmp_u_arr [3], 'type_id' => $tmp_u_arr [4], 'fstatus' => $tmp_u_arr [5], 'login_time' => time () );
							//匹配自动登陆
							$this->session->set_userdata ( 'rsh_user', $user );
							return $user;
						} else {
							return false;
						}
					} else {
						return false;
					}
				} else {
					return false;
				}
			} else {
				return false;
			}
		}
	}
	
	/**
	 * 检测用户名是否存在
	 * @param string $username 用户名
	 * @return int 查询结果的行数
	 */
	function check_username($username = '') {
		$this->db->select ( 'user_id' );
		$this->db->where ( 'username', $username );
		$query = $this->db->get ( 'rsh_user' );
		//$this->db->select('user_id')->from('user')->where('username', $username)->limit(1, 0);
		return $query->num_rows ();
	}
	
	/**
	 * 检测邮箱是否存在
	 * @param string $email 邮箱
	 * @return int 查询结果的行数
	 */
	function check_email($email = '') {
		$this->db->select ( 'user_id' );
		$this->db->where ( 'email', $email );
		$query = $this->db->get ( 'rsh_user' );
		return $query->num_rows ();
	}
	
	function get_username_by_id($user_id = '') {
		$user_id = intval ( $user_id );
		if ($user_id < 1) {
			return false;
		}
				
		$this->db->select ( '*' );
		$this->db->where ( 'user_id', $user_id );
		$query = $this->db->get ( 'rsh_user' );
		return $query->row_array();
	}
	
	function get_realname_by_id($user_id = '') {
		$user_id = intval ( $user_id );
		if ($user_id < 1) {
			return false;
		}
		$this->db->select ( 'user_id,realname,real_status,phone' );
		$this->db->where ( 'user_id', $user_id );
		$query = $this->db->get ( 'rsh_user' );
		return $query->row_array ();
	}
	
	function get_all_by_id($user_id = '') {
		$user_id = intval ( $user_id );
		if ($user_id < 1) {
			return false;
		}
		$this->db->where ( 'user_id', $user_id );
		$query = $this->db->get ( 'rsh_user' );
		return $query->row_array ();
	}
	
	/**
	 * 根据用户名获取用户ID 缓存
	 * @param $username
	 */
	function get_userid_by_username($username = '') {
		if ($username == '') {
			return 0;
		}
		$this->db->select ( 'user_id' );
		$this->db->where ( 'username', $username );
		$query = $this->db->get ( 'rsh_user' );
		$user_ret = $query->row_array ();
		if (count ( $user_ret ) == 0) {
			return 0;
		}
		return $user_ret ['user_id'];
	}
	
	/**
	 * 登陆验证
	 * @param string $user 用户名/Email
	 * @param string $password 密码
	 * @return array/false
	 */
	function check_login($user = '', $password = '') {
		$password = md5 ( md5 ( $password ) );
		$sql = "select user_id,username,realname,phone,email,type_id,fstatus,
		invest_score from rsh_user where ((email='{$user}' and email_status='1') or (phone='{$user}' and phone_status='1') or (username='{$user}') ) and password='$password'";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () == 1) {
			return $query->row_array ();
		} else {
			return false;
		}
	}
	
	/**
	 * 后台登陆验证
	 * @param string $user 用户名/Email
	 * @param string $password 密码
	 * @return array/false
	 */
	function admin_check_login($user = '', $password = '', $smscode = '') {
		$password = md5 ( md5 ( $password ) );
		$ctime = time () - 24 * 60 * 60;
		$sql = "select user_id,username,email,type_id,fstatus from rsh_user where 
				(username='{$user}' or email='{$user}') and password='{$password}' 
				and smscode='{$smscode}' and smstime>{$ctime} limit 1";
		/*
		$sql = "select user_id,username,email,type_id,fstatus from rsh_user where 
				(username='{$user}' or email='{$user}') and password='{$password}' 
				limit 1";
		*/
		$query = $this->db->query ( $sql );
		if ($query->num_rows () > 0) {
			return $query->row_array ();
		} else {
			return false;
		}
	}
	
	/**
	 * 获取登录密码输错次数
	 * @param string $user 用户名/Email
	 */
	function check_login_errnum($user = '') {
		$sql = "select error_num from rsh_user where (email='{$user}' and email_status='1') or (phone='{$user}' and phone_status='1') or (username='{$user}')";
		$query = $this->db->query ( $sql );
		if ($query->num_rows () == 1) {
			return $query->row_array ();
		} else {
			return false;
		}
	}
	
	/**
	 * 密码输入错误次数+1
	 * @param $user 用户名/Email
	 */
	function set_error_num($user = '') {
		/*tangc 死锁问题*/
		$this->db->trans_strict(FALSE);
   	 	$this->db->trans_begin();
    	
		$sql = "update rsh_user set error_num=error_num+1 where username='{$user}' or email='{$user}'";
		$query = $this->db->query ( $sql );
		
		if ($this->db->trans_status() === TRUE) {
	      	$this->db->trans_commit();
	      	return 1;
	    } else {
	        $this->db->trans_rollback();
	        //@todo 异常处理部分
	        return 0;
	    }
	}
	
	/**
	 * 登陆支付密码
	 * @param string $user 用户名/Email
	 * @param string $password 密码
	 * @return array/false
	 */
	function check_paypassword($user_id = '', $paypassword = '') {
		$paypassword = md5 ( md5 ( $paypassword ) );
		$sql = "select count(*) as count from rsh_user where user_id=$user_id and paypassword='{$paypassword}' limit 1";
		$query = $this->db->query ( $sql );
		$result = $query->row_array ();
		return $result ['count'];
	}
	
	function user_attestation_status($parms = array(), $per_page = 0, $page_size = 10) {
		$tmp = array ();
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username' || $key == 'realname') {
					$tmp [] = "`{$key}` like '%{$val}%'";
				} else {
					$tmp [] = "`{$key}` = '{$val}'";
				}
			}
		}
		$where = '';
		$tmp && $where = " where " . implode ( ' and ', $tmp );
		
		$sql = "SELECT p1.user_id,p1.username,p1.realname,p1.real_status,p1.email_status,p1.phone_status,p1.scene_status,p2.vip_status,p2.kefu_userid,p2.vip_verify_time,p1.add_time
FROM rsh_user AS p1 
LEFT JOIN rsh_user_vip AS p2 ON p1.user_id=p2.user_id {$where} ORDER BY p1.user_id DESC LIMIT {$per_page},{$page_size};";
		$query = $this->db->query ( $sql );
		$data ['list'] = $query->result_array ();
		$sql = "SELECT COUNT(p1.user_id) AS count FROM rsh_user AS p1 LEFT JOIN rsh_user_vip AS p2 ON p1.user_id=p2.user_id {$where}";
		$query = $this->db->query ( $sql );
		$data ['count'] = $query->row ()->count;
		return $data;
	}
	
	function user_attestation_info($user_id = 0) {
		$sql = "SELECT p1.user_id,p1.username,p1.logintime,p1.add_time,p1.lasttime,
p1.realname,p1.sex,p1.card_type,p1.card_id,p1.card_pic1,p1.card_pic2,
p1.province,p1.city,p1.area,p1.nation,p1.real_status,p1.email,p1.email_status,
p1.video_status,p1.scene_status,p1.phone,p1.phone_status,p1.birthday,p1.smscode,
p2.vip_status,p2.vip_verify_remark,p2.kefu_userid,
p3.marry,p3.child,p3.education_record,p3.house,p3.car,
p3.company_name,p3.company_type,p3.company_industry,p3.company_office,
p3.company_employee,p3.company_tel,p3.company_address,p3.company_weburl,
p3.linkman1,p3.relation1,p3.phone1,p3.linkman2,p3.relation2,p3.phone2 
FROM rsh_user AS p1 
LEFT JOIN rsh_user_vip AS p2 ON p1.user_id=p2.user_id 
LEFT JOIN rsh_user_info AS p3 ON p1.user_id=p3.user_id 
WHERE p1.user_id = '{$user_id}' LIMIT 1";
		$query = $this->db->query ( $sql );
		return $query->row_array ();
	}
	
	function real_status_list($parms = array(), $per_page = 0, $page_size = 10) {
		$sql = "select user_id,username,realname,sex,nation,birthday,card_type,card_id,province,city,area,card_pic1,card_pic2,real_status from rsh_user";
		$tmp = array ();
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username' || $key == 'realname') {
					$tmp [] = "`{$key}` like '%{$val}%'";
				} else {
					$tmp [] = "`{$key}` = '{$val}'";
				}
			}
		}
		$where = '';
		$tmp && $where = " where " . implode ( ' and ', $tmp );
		$sql .= $where;
		$order = " order by real_status desc,user_id desc";
		$sql .= $order;
		$sql .= " limit {$per_page},$page_size";
		$query = $this->db->query ( $sql );
		$data ['list'] = $query->result_array ();
		$sql = "select count(user_id) as count from rsh_user" . $where;
		$query = $this->db->query ( $sql );
		$data ['count'] = $query->row ()->count;
		return $data;
	}
	
	/***改版*************************************************************************************************/
	
	/**
	 * 添加用户
	 * @param array $data
	 * @return int $user_id
	 */
	function add($data = array()) {
		$this->db->insert ( 'rsh_user', $data );
		return $this->db->insert_id ();
	}
	
	function get_by_uid($user_id) {
		$key = 'rsh_user_' . $user_id;
		$this->mycache->delete($key);//@todo
		$row = $this->mycache->get_array ( $key );
		if ($row == false) {
			$this->db->where ( 'user_id', $user_id );
			$query = $this->db->get ( 'rsh_user' );
			$row = $query->row_array ();
			if ($row) {
				$this->mycache->set_array ( $key, $row ,60);
			}
		}
		return $row;
	}
	
	/**
	 * 获取单条记录
	 */
	function get_one($where = array(), $select = '*') {
		$this->db->select ( $select );
		if ($where) {
			$this->db->where ( $where );
			var_dump($where);
		}
		$this->db->limit ( 1 );
		$query = $this->db->get ( 'rsh_user' );
		return $query->row_array ();
	}
	
	/**
	 * 获取列表记录
	 */
	public function get_list($where = array(), $select = '*') {
		$this->db->select ( $select );
		if ($where != '') {
			$this->db->where ( $where );
		}
		$this->db->order_by('add_time',desc);
		$query = $this->db->get ( 'rsh_user' );
		return $query->result_array ();
	}

        public function get_invist_num($user_id,$page=0,$page_size=10){
                $sql="select username,phone,add_time,invite_money from rsh_user where invite_userid=$user_id order by add_time desc limit $page,$page_size";
                $query = $this->db->query($sql);
                $result['invite_user_list']=$query->result_array();

                $sql = "select count(invite_userid) as count from rsh_user where invite_userid=$user_id";
                $query = $this->db->query($sql);
                $result['count'] = $query->row()->count;
                return $result;
        }
	
	/**
	 * 修改用户信息
	 * @param array $data
	 * @return 影响行数/false
	 */
	function update($data = array(), $where = array()) {
			/*tangc 死锁问题*/
		$this->db->trans_strict(FALSE);
    	$this->db->trans_begin();
    	
		$this->db->where ( $where );
		$this->db->update ( 'rsh_user', $data );
		
		if ($this->db->trans_status() === TRUE) {
			$key = 'rsh_user_' . $where ['user_id'];
			$this->mycache->delete ( $key );
			//删除安全等级缓存
			$key3 = 'anquan_'.$where['user_id'];
			$this->mycache->delete($key3);
        	$this->db->trans_commit();
        	return 1;
	    } else {
	        $this->db->trans_rollback();
	        //@todo 异常处理部分
	        return 0;
	    }
	}
	
	function get_user_list($parms = array(), $per_page = 0, $page_size = 10) {
		$tmp = array ();
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username' || $key == 'realname') {
					$tmp [] = "`{$key}` like '%{$val}%'";
				} elseif ($key == 'add_time') {
					$user_start = strtotime ( $val . ' 00:00:00' );
					$user_end = strtotime ( $val . ' 23:59:59' );
					$tmp [] = "`{$key}` >= '{$user_start}'";
					$tmp [] = "`{$key}` <= '{$user_end}'";
				} else {
					$tmp [] = "`{$key}` = '{$val}'";
				}
			}
		}
		$where = '';
		$tmp && $where = " where " . implode ( ' and ', $tmp );
		$order = "order by user_id desc";
		$limit = " limit {$per_page},{$page_size}";
		$sql = "select * from rsh_user {$where} {$order} {$limit}";
		
		$query = $this->db->query ( $sql );
		
		//echo $sql;exit;
		$ulist = $query->result_array ();
		
		$user_list = array ();
		foreach ( $ulist as $uv ) {
			//累计充值金额
			//$mode = $uv['user_id']%100;
			$mode = substr ( strval ( $uv ['user_id'] ), - 1, 1 );
			
			$recharge_sql = "select sum(money) as money from rsh_account_recharge where fstatus = 1 and user_id = {$uv ['user_id']}";
			$rcount = $this->db->query ( $recharge_sql );
			$r = $rcount->row ();
			$uv ['recharge_money'] = $r->money;
			
			//累计投标数量&&累计投标金额
			$tender_sql = "select SUM(account) as tender_money, count(id) as count from rsh_borrow_tender where fstatus = 1 and  user_id =" . $uv ['user_id'];
			$tcount = $this->db->query ( $tender_sql );
			$t = $tcount->row ();
			$uv ['tender_money'] = $t->tender_money;
			$uv ['tender_count'] = $t->count;
			
			//账户余额
			$account_sql = "select use_money from rsh_account where  user_id=" . $uv ['user_id'];
			$aquery = $this->db->query ( $account_sql );
			$arow = $aquery->row_array ();
			$uv ['use_money'] = $arow ['use_money'];
			
			//****** by liub 20141214 修改会员等级
			$this->load->model ( 'Invest_model' );
			if($uv ['invest_rank_id'] ==0 ){
				$pic = $this->Invest_model->get_pic ( $uv ['invest_score'] );
				$uv ['vip_pic'] = $pic;
			}else{
				$pic = $this->Invest_model->get_invest_rank_by_id ( $uv ['invest_rank_id'] );
				$uv ['vip_pic'] = $pic;
			}
			
			//推荐人
			$iccount_sql = "select username from rsh_user where user_id=" . $uv ['invite_userid'];
			$iquery = $this->db->query ( $iccount_sql );
			$irow = $iquery->row_array ();
			$uv ['invite_uname'] = $irow ['username'];
			
			$user_list [] = $uv;
		}
		
		$data ['user_list'] = $user_list;
		
		$sqlcount = "select count(user_id) as count from rsh_user";
		$squery = $this->db->query ( $sqlcount );
		$data ['user_count'] = $squery->row ()->count;
		return $data;
	}
	
	/**
	 * 分页列表 for admin
	 */
	function get_user_list1($parms = array(), $per_page = 0, $page_size = 10) {
		$tmp = array ();
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username' || $key == 'realname') {
					$tmp [] = "`{$key}` like '%{$val}%'";
				} elseif ($key == 'is_admin') {
					$tmp [] = "`type_id` != '2'";
				} elseif ($key == 'add_time') {
					$user_start = strtotime ( $val . ' 00:00:00' );
					$user_end = strtotime ( $val . ' 23:59:59' );
					$tmp [] = "`{$key}` >= '{$user_start}'";
					$tmp [] = "`{$key}` <= '{$user_end}'";
				} else {
					$tmp [] = "`{$key}` = '{$val}'";
				}
			}
		}
		
		$select = "user_id,username,email,realname,phone,qq,sex,province,city,area,add_time";
		$where = '';
		$tmp && $where = " where " . implode ( ' and ', $tmp );
		$order = "order by user_id desc";
		$limit = " limit {$per_page},{$page_size}";
		$sql = "select {$select} from rsh_user {$where} {$order} {$limit}";
		$query = $this->db->query ( $sql );
		$data ['user_list'] = $query->result_array ();
		$sql = "select count(user_id) as count from rsh_user {$where}";
		$query = $this->db->query ( $sql );
		$data ['user_count'] = $query->row ()->count;
		return $data;
	}
	
	function get_name_list() {
		
		$key = 'rsh_name_list';
		$list = $this->mycache->get_array ( $key );
		if (! $list) {
			
			$this->db->select ( 'user_id,username' );
			$query = $this->db->get ( 'rsh_user' );
			$result = $query->result_array ();
			$list = array ();
			if ($result) {
				foreach ( $result as $v ) {
					$list [$v ['user_id']] = $v ['username'];
				}
				$this->mycache->set_array ( $key, $list);
			}
		}
		
		return $list;
	}
	
	/**
	 * 客服列表
	 */
	function get_kefu_list() {
		$key = 'rsh_kefu_list';
		$this->mycache->delete ( $key );
		$tmpList = $this->mycache->get_array ( $key );
		
		if ($tmpList == false) {
			$sql = "SELECT p1.user_id,p1.username,p1.realname,p1.email,p1.phone,p1.sex,p1.qq,p2.tel,p2.user_desc 
FROM rsh_user AS p1 LEFT JOIN rsh_user_info AS p2 ON p1.user_id=p2.user_id WHERE p1.type_id=3 AND p1.fstatus=1";
			$query = $this->db->query ( $sql );
			$result = $query->result_array ();
			
			$list = array ();
			if ($result) {
				foreach ( $result as $v ) {
					$list [$v ['user_id']] = $v;
				}
			}
			//排序写死
			$tmpList = $list;
			$this->mycache->set_array ( $key, $tmpList );
		}
		return $tmpList;
	}
	
	/**
	 * 新总注册人数统计
	 */
	function reg_num() {
		$key = 'rsh_reg_num_' . date ( 'Ymd' );
		$ret = $this->mycache->get_array ( $key );
		if (! $ret) {
			$ret = array ();
			$today = strtotime ( date ( 'Y-m-d' ) );
			$yestoday = $today - 3600 * 24;
			$sql = "select count(user_id) as num from rsh_user";
			$query = $this->db->query ( $sql );
			$row = $query->row_array ();
			$ret ['total'] = $row ['num'];
			$sql = "select count(user_id) as num from rsh_user where add_time>={$yestoday} and add_time<{$today}";
			$query = $this->db->query ( $sql );
			$row = $query->row_array ();
			$ret ['new'] = $row ['num'];
			$this->mycache->set_array ( $key, $ret, 3600 * 24 );
		}
		return $ret;
	}
	
	/**
	 * 添加登录日志
	 * @param $data
	 */
	function add_user_login_log($data = array()) {
		$this->db->insert ( 'rsh_user_login', $data );
		return $this->db->insert_id ();
	}
	
	function update_invest_rank($user_id,$invest) {
		if(!$invest) $invest = 0;
		$sql = "select id from rsh_invest_rank where minscore<=".$invest." and maxscore>".$invest;
		$query = $this->db->query ( $sql );
		$row = $query->row_array ();
		if($row){
			/*tangc 死锁问题*/
			$this->db->trans_strict(FALSE);
    		$this->db->trans_begin();
    	
			$sql = "update rsh_user set invest_rank_id ='".$row['id']."' where user_id='".$user_id."'";
			$query = $this->db->query ( $sql );
			
			if ($this->db->trans_status() === TRUE) {
				//删除安全等级缓存
				$key3 = 'anquan_'.$user_id;
				$this->mycache->delete($key3);
	        	$this->db->trans_commit();
	        	return 1;
		    } else {
		        $this->db->trans_rollback();
		        //@todo 异常处理部分
		        return 0;
		    }
		}
	}
	
	function vip_status_list($parms = array(), $per_page = 0, $page_size = 10) {
		$tmp = array ();
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username' || $key == 'realname') {
					$tmp [] = "`{$key}` like '%{$val}%'";
				} else {
					$tmp [] = "`{$key}` = '{$val}'";
				}
			}
		}
		$sql = "select v.user_id,u.username,v.kefu_userid,v.vip_status,v.vip_verify_remark,v.kefu_addtime from rsh_user_vip v left join rsh_user u on v.user_id = u.user_id";
		$where = '';
		$tmp && $where = " where " . implode ( ' and ', $tmp );
		$sql .= $where;
		$order = " order by v.kefu_addtime desc";
		$sql .= $order;
		$sql .= " limit {$per_page},$page_size";
		$query = $this->db->query ( $sql );
		$data ['list'] = $query->result_array ();
		$sql = "select count(*) as count from rsh_user_vip v left join rsh_user u on v.user_id = u.user_id" . $where;
		$query = $this->db->query ( $sql );
		$data ['count'] = $query->row ()->count;
		return $data;
	
	}
	
	function vip_count_list($parms = array()) {
		$tmp = array ();
		
		if (isset ( $parms ['kefu_userid'] ) && $parms ['kefu_userid']) {
			$tmp [] = " kefu_userid = '{$parms['kefu_userid']}'";
		}
		
		if (isset ( $parms ['strtime'] ) && $parms ['strtime']) {
			$tmp [] = " kefu_addtime>'{$parms['strtime']}'";
		}
		
		if (isset ( $parms ['endtime'] ) && $parms ['endtime']) {
			$tmp [] = " kefu_addtime<'{$parms['endtime']}'";
		}
		
		$where = '';
		$tmp && $where = " where " . implode ( ' and ', $tmp );
		
		$sql = "select count(*) as count from rsh_user_vip" . $where . ' and vip_status=1';
		$query = $this->db->query ( $sql );
		$data ['count'] = $query->row ()->count;
		return $data;
	}
	
	function invite_list($parms = array(), $data = array()) {
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username') {
					$tmp .= " and p1.{$key} like '%{$val}%'";
				} elseif ($key == 'invite_username') {
					$invite_userid = $this->get_userid_by_username ( $val );
					$tmp .= "and p1.invite_userid =" . $invite_userid;
				}
			}
		}
		
		$where = ' WHERE p3.fstatus=3 AND p3.ftype!=5 AND p1.invite_userid>0 ';
		$tmp && $where = $where . $tmp;
		
		$order = " GROUP BY p1.user_id order by p1.user_id desc ";
		$sql = 'SELECT SUM(p2.account) as money, count(p2.id) as count,p1.user_id,p1.username,p1.invite_userid,p1.invite_money 
				FROM rsh_user AS p1 
				LEFT JOIN rsh_borrow_tender AS p2 ON p1.user_id=p2.user_id
				LEFT JOIN rsh_borrow AS p3 ON p2.borrow_id=p3.id' . $where . $order;
		
		if (isset ( $data ['limit'] ) && $data ['limit'] == 'all') {
			$query = $this->db->query ( $sql );
			$data ['user_list'] = $query->result_array ();
			return $data;
		} else {
			$sql .= " limit {$data['per_page']},{$data['page_size']}";
			$query = $this->db->query ( $sql );
			$data ['user_list'] = $query->result_array ();
		}
		
		$query = $this->db->query ( $sql );
		$result = $query->result_array ();
		
		if ($result) {
			foreach ( $result as $v ) {
				$invite_info = $this->get_by_uid ( $v ['invite_userid'] );
				$v ['invite_username'] = $invite_info ['username'];
				$user_list [] = $v;
			}
		}
		$data ['user_list'] = $user_list;
		
		$sql = "select count(DISTINCT(p1.user_id)) as count FROM rsh_user AS p1 
LEFT JOIN rsh_borrow_tender AS p2 ON p1.user_id=p2.user_id
LEFT JOIN rsh_borrow AS p3 ON p2.borrow_id=p3.id 
" . $where;
		
		$query = $this->db->query ( $sql );
		$data ['user_count'] = $query->row ()->count;
		
		return $data;
	}
	
	//****** by liub 20141113  会员数据统计
	//TODO 待优化
	/**
	 * VIP 时间已 添加时间 kefu_addtime为基础
	 * 
	 * */
	
	function statistics_user($data) {
		
		if (isset ( $data ['where'] ['strtime'] ) && $data ['where'] ['strtime'] != 0) {
			$begintime = strtotime ( $data ['where'] ['strtime'] . ' 00:00:00' );
		} else {
			// 获取第一个注册会员日期
			$_sql = " select min(u.add_time) as strtime from rsh_user u  ";
			$query = $this->db->query ( $_sql );
			$time = $query->row ()->strtime;
			$begintime = strtotime ( date ( 'Y-m-d', $time ) . ' 00:00:00' );
		}
		
		if (isset ( $data ['where'] ['endtime'] ) && $data ['where'] ['endtime'] != 0) {
			$endtime = strtotime ( $data ['where'] ['endtime'] . ' 23:59:59' );
		} else {
			// 以今天为汇总最后日期
			$endtime = strtotime ( date ( 'Y-m-d', time () ) . ' 23:59:59' );
		}
		
		$_where = " where u.add_time<={$endtime} and u.add_time>={$begintime} ";
		$limit = " limit {$data['page_start']},{$data['page_size']}";
		$_order = " order by u.add_time desc ";
		$sql = "select distinct DATE_FORMAT(FROM_UNIXTIME(u.add_time),'%Y%m%d')  as time
		 	from rsh_user u {$_where} {$_order} {$limit}";
		
		$query = $this->db->query ( $sql );
		$ulist = $query->result_array ();
		
		$user_list = array ();
		
		foreach ( $ulist as $uv ) {
			// 	累计会员数,传入时间戳A
			$_sql4 = "select 
				count(u.user_id) as total_user_acount
				from rsh_user u   
				where DATE_FORMAT(FROM_UNIXTIME(u.add_time),'%Y%m%d') <={$uv['time']}
				and u.is_self = 0";
			$query = $this->db->query ( $_sql4 );
			$uv ['total_user_acount'] = $query->row ()->total_user_acount;
			
			//echo $_sql4;
			//exit();
			// 	累计VIP会员数,传入时间戳A
			$_sql5 = "select 
				count(u.user_id) as total_vipu_count
				from rsh_user u LEFT JOIN rsh_user_vip uv ON u.user_id = uv.user_id
				where  DATE_FORMAT(FROM_UNIXTIME(kefu_addtime),'%Y%m%d') <={$uv['time']}
				and uv.vip_status = 1 and u.is_self = 0";
			$query = $this->db->query ( $_sql5 );
			$uv ['total_vipu_count'] = $query->row ()->total_vipu_count;
			
			//  新增会员数,传入时间戳A
			$_sql1 = " select 
			count(u.user_id) as user_count 
			from rsh_user u  
			where u.is_self = 0 
			and DATE_FORMAT(FROM_UNIXTIME(u.add_time),'%Y%m%d') ={$uv['time']}";
			$query = $this->db->query ( $_sql1 );
			$uv ['user_count'] = $query->row ()->user_count;
			
			// 新增充值会员数,传入时间戳A
			$_sql2 = "select
			count(distinct r.user_id) as user_charge_count
			from rsh_account_recharge r LEFT JOIN rsh_user u ON  u.user_id = r.user_id
			where u.is_self = 0
			and DATE_FORMAT(FROM_UNIXTIME(r.verify_time),'%Y%m%d') ={$uv['time']}";
			$query = $this->db->query ( $_sql2 );
			$uv ['user_charge_count'] = $query->row ()->user_charge_count;
			
			// 	新增VIP会员数,传入时间戳A
			$_sql3 = "select 
			count(u.user_id) as vip_user_count
			from rsh_user u LEFT JOIN rsh_user_vip uv ON u.user_id = uv.user_id
			where uv.vip_status = 1 and u.is_self = 0
			and DATE_FORMAT(FROM_UNIXTIME(kefu_addtime),'%Y%m%d') ={$uv['time']}";
			$query = $this->db->query ( $_sql3 );
			$uv ['vip_user_count'] = $query->row ()->vip_user_count;
			
			// 修改前台显示日期
			$uv ['time'] = strtotime ( $uv ['time'] . ' 00:00:00' );
			$user_list [] = $uv;
		}
		
		$data ['user_list'] = $user_list;
		
		$sqlcount = "select count(time) as count from ( select distinct DATE_FORMAT(FROM_UNIXTIME(u.add_time),'%Y%m%d')  as time from rsh_user u {$_where}) as vt";
		$squery = $this->db->query ( $sqlcount );
		$data ['time_count'] = $squery->row ()->count;
		return $data;
	
	}
	
	//****** by liub 20141113  兼容版本
	function get_locku_list($parms = array(), $per_page = 0, $islock = 0, $page_size = 10) {
		$tmp = array ();
		foreach ( $parms as $key => $val ) {
			if ($val) {
				if ($key == 'username' || $key == 'realname') {
					$tmp [] = "`{$key}` like '%{$val}%'";
				} elseif ($key == 'add_time') {
					$user_start = strtotime ( $val . ' 00:00:00' );
					$user_end = strtotime ( $val . ' 23:59:59' );
					$tmp [] = "`{$key}` >= '{$user_start}'";
					$tmp [] = "`{$key}` <= '{$user_end}'";
				} else {
					$tmp [] = "`{$key}` = '{$val}'";
				}
			}
		}
		$where = "where 1 = 1" ;
		if (! empty ( $tmp )) {
			$where .= " and" . implode ( ' and ', $tmp );
		}
		//$tmp && $where = " and " . implode(' and ', $tmp);
		$order = "order by user_id desc";
		$limit = " limit {$per_page},{$page_size}";
		
		$sql = "select * from rsh_user {$where} {$order} {$limit}";
		
		$query = $this->db->query ( $sql );
		$ulist = $query->result_array ();
		
		$user_list = array ();
		foreach ( $ulist as $uv ) {
			//累计充值金额
			//$mode = $uv['user_id']%100;
			$mode = substr ( strval ( $uv ['user_id'] ), - 1, 1 );
			
			$recharge_sql = "select sum(money) as money from rsh_account_log_" . $mode . " where  ftype=" . RECHARGE;
			$rcount = $this->db->query ( $recharge_sql );
			$r = $rcount->row ();
			$uv ['recharge_money'] = $r->money;
			
			//累计投标数量&&累计投标金额
			$tender_sql = "select SUM(account) as tender_money, count(id) as count from rsh_borrow_tender where  user_id =" . $uv ['user_id'];
			$tcount = $this->db->query ( $tender_sql );
			$t = $tcount->row ();
			$uv ['tender_money'] = $t->tender_money;
			$uv ['tender_count'] = $t->count;
			
			//账户余额
			$account_sql = "select use_money from rsh_account where  user_id=" . $uv ['user_id'];
			$aquery = $this->db->query ( $account_sql );
			$arow = $aquery->row_array ();
			$uv ['use_money'] = $arow ['use_money'];
			
			//TODO****** by liub 20141120  需修改 会员等级
			$this->load->model ( 'Credit_model' );
			$u_credit = $this->Credit_model->get_row ( $uv ['user_id'] );
			$credit_value = isset ( $u_credit ['credit_value'] ) ? $u_credit ['credit_value'] : 0;
			$uv ['pic'] = $this->Credit_model->get_pic ( $credit_value );
			$uv ['credit_value'] = $credit_value;
			
			//推荐人
			$iccount_sql = "select username from rsh_user where user_id=" . $uv ['invite_userid'];
			$iquery = $this->db->query ( $iccount_sql );
			$irow = $iquery->row_array ();
			$uv ['invite_uname'] = $irow ['username'];
			
			$user_list [] = $uv;
		}
		
		$data ['user_list'] = $user_list;
		
		$sqlcount = "select count(user_id) as count from rsh_user  {$where} {$order} {$limit}";
		$squery = $this->db->query ( $sqlcount );
		$data ['user_count'] = $squery->row ()->count;
		return $data;
	}
	
	/*** by  liub 20141124
	 * 
	 * 统计会员推广数据(按会员id统计)
	 * 
	 * $data['statime','endtime','page_start','page_size','username']
	 */
	function get_cpsuser_list($data) {
		
		$_where = " where 1=1 ";
		if (isset ( $data ['where'] ['strtime'] ) && $data ['where'] ['strtime'] != 0) {
			$begintime = strtotime ( $data ['where'] ['strtime'] . ' 00:00:00' );
			$_where .= " and  u.add_time>={$begintime} ";
		}
		
		if (isset ( $data ['where'] ['endtime'] ) && $data ['where'] ['endtime'] != 0) {
			$endtime = strtotime ( $data ['where'] ['endtime'] . ' 23:59:59' );
			$_where .= " and  u.add_time<={$endtime}";
		}
		//$_where .= " and u.username ='viviqin'";
		if (isset ( $data ['where'] ['username'] ) && $data ['where'] ['username'] != '') {
			$_where .= " and u.username ='{$data['where']['username']}'";
		}
				if (isset ( $data ['where'] ['phone'] ) && $data ['where'] ['phone'] != '') {
			$_where .= " and u.phone ='{$data['where']['phone']}'";
		}
		
		$_rightselect = " select invite_userid,count(invite_userid) as totalcpsu from rsh_user 
							where invite_userid !=0 
							group by invite_userid ";
		
		$_select = "select u.user_id,u.username,u.realname,u.add_time, vt.totalcpsu,(case when vt2.cps_money is not null then vt2.cps_money else  0 end) as cpsmoney 
					from rsh_user as u 
					RIGHT JOIN ({$_rightselect}) as vt ON u.user_id = vt.invite_userid 
					LEFT JOIN rsh_account as vt2 ON u.user_id = vt2.user_id";
		
		$_order = " order by u.user_id desc ";
		$_limit = " limit {$data['page_start']},{$data['page_size']}";
		$_sql = $_select . $_where . $_order . $_limit;
		
		$query = $this->db->query ( $_sql );
		$result = $query->result_array ();
		
		// 遍历 进行汇总查询   “充值会员数 、推广会员的理财总额 、还款总额”
		$user_list = array ();
		foreach ( $result as $value ) {
			// 其中的充值会员数
			$_rechg = "select (case when count(1) is not null then count(1) else 0 end) as rechg_count from (
							select 1 from rsh_account_recharge  as  r
							where r.fstatus = 1 
							and exists ( select 1 from rsh_user u where invite_userid = '{$value['user_id']}' and r.user_id = u.user_id)
							group by user_id ) as vt";
			$rechgcount = $this->db->query ( $_rechg );
			$irow = $rechgcount->row_array ();
			$value ['rechg_count'] = $irow ['rechg_count'];
			// 会员理财总金额
			$_licai = " select (case when sum(account) is not null then sum(account) else 0 end ) as licaimoney from rsh_borrow_tender t
						where fstatus=1 
						and exists ( select 1 from rsh_user u where invite_userid ='{$value['user_id']}' and t.user_id = u.user_id)";
			$licaimoney = $this->db->query ( $_licai );
			$mrow = $licaimoney->row_array ();
			$value ['licaimoney'] = $mrow ['licaimoney'];
			
			//TODO 暂无还款总额
			

			// 汇总
			$user_list [] = $value;
		}
		
		$data ['user_list'] = $user_list;
		$sqlcount = "select count(1) as count from (select 1 from rsh_user as u 
						RIGHT JOIN ( select invite_userid from rsh_user where invite_userid !=0 group by invite_userid ) as vt 
						ON u.user_id = vt.invite_userid 
						LEFT JOIN rsh_account as vt2 
						ON u.user_id = vt2.user_id where 1=1 ) as vt3";
		$squery = $this->db->query ( $sqlcount );
		$data ['user_count'] = $squery->row ()->count;
		return $data;
	}
	
	/*** by liub 20141124 
	 * 
	 * 查看推广用户的所有所推会员
	 * 
	 * */
	function get_cpuuserdetail_list($user_id) {
		
		//序号,用户名,注册时间 ,信用等级,注册时长(天),充值总额 ,还款总额,佣金总额
		$_sql = " select user_id,username,add_time from rsh_user u where invite_userid = {$user_id}";
		$_and = " ";
		$_order = " order by u.user_id desc ";
		//$_limit = " limit {$page_start},{$page_size}";
		$_sql = $_sql . $_and . $_order; //.$_limit;
		

		$query = $this->db->query ( $_sql );
		$result = $query->result_array ();
		
		$user_list = array ();
		foreach ( $result as $v ) {
			// 充值总额
			$_rechg = "select (case when sum(money) is not null then sum(money) else 0 end ) as rechg_money from (
							select money from rsh_account_recharge  as  r
							where r.fstatus = 1 and r.user_id = {$v['user_id']}) as vt";
			
			$rechgcount = $this->db->query ( $_rechg );
			$irow = $rechgcount->row_array ();
			$v ['rechg_money'] = $irow ['rechg_money'];
			
			// 会员理财总金额
			$_licai = " select (case when sum(account) is not null then sum(account) else 0 end ) as licaimoney from rsh_borrow_tender t
						where fstatus=1 and t.user_id =  {$v['user_id']}";
			$licaimoney = $this->db->query ( $_licai );
			$mrow = $licaimoney->row_array ();
			$v ['licaimoney'] = $mrow ['licaimoney'];
			
			// 佣金总额
			$_yongjin = "select cps_money from rsh_account where user_id = {$v['user_id']}";
			$yongjinmoney = $this->db->query ( $_yongjin );
			$yrow = $yongjinmoney->row_array ();
			$v ['yongjinmoney'] = $yrow ['cps_money'];
			//TODO 暂无还款总额	
			

			// 汇总
			$user_list [] = $v;
		}
		
		$vdata ['user_list'] = $user_list;
		$sqlcount = "select count(user_id) as count from rsh_user where invite_userid = {$user_id}";
		
		$squery = $this->db->query ( $sqlcount );
		$vdata ['user_count'] = $squery->row ()->count;
		
		return $vdata;
	}
	
	/*** by liub 20141124
	 * 
	 * 佣金发放统计，按月，按年，按季度
	 * */
	function get_cpsmoney_list($data) {
		// 以月份+年限进行查询 一个月的数据
		$start_time = $data ['start_time'];
		$end_time = $data ['end_time'];
		
		// CPS会员数 
		$_sql_cpsu = "select count(invite_userid) as cpsu_count from ( select invite_userid  from rsh_user 
						where invite_userid != 0 and add_time>{$start_time} and add_time<{$end_time}
						group by invite_userid) as vt";
		//echo $_sql_cpsu;
		//exit();
		

		$query = $this->db->query ( $_sql_cpsu );
		$ret1 = $query->row_array ();
		$retData ['cpsu_count'] = $ret1 ['cpsu_count'];
		
		// 推广注册会员数 
		$_sql_regu = "select count(user_id) as regu_count from rsh_user where invite_userid != 0 and add_time>{$start_time} and add_time<{$end_time} ";
		$query = $this->db->query ( $_sql_regu );
		$ret2 = $query->row_array ();
		$retData ['regu_count'] = $ret2 ['regu_count'];
		
		// 推广充值会员数
		$_sql_rechgu = "select (case when count(1) is not null then count(1) else 0 end) as rechg_count from (
			select 1 from rsh_account_recharge  as  r 
			where r.fstatus = 1 and r.add_time>{$start_time} and r.add_time<{$end_time}
			and exists ( select 1 from rsh_user u where invite_userid !=0  and r.user_id = u.user_id)
			group by r.user_id ) as vt ";
		
		$query = $this->db->query ( $_sql_rechgu );
		$ret3 = $query->row_array ();
		$retData ['rechg_count'] = $ret3 ['rechg_count'];
		
		// 推广的会员理财总金额
		$_sql_licaimoney = "select (case when sum(account) is not null then sum(account) else 0 end ) as licaimoney from rsh_borrow_tender t
				where t.fstatus=1  and  t.add_time>{$start_time} and t.add_time<{$end_time}
			and exists ( select 1 from rsh_user u where invite_userid !=0 and t.user_id = u.user_id)";
		$query = $this->db->query ( $_sql_licaimoney );
		$ret4 = $query->row_array ();
		$retData ['licaimoney'] = $ret4 ['licaimoney'];
		
		// 应支佣金金额
		$_sql_cpsmoney = "select sum(cps_money) as sum_money from rsh_account as t where exists (
						select 1 from rsh_user as u where u.add_time>{$start_time} and u.add_time<{$end_time}
						and invite_userid != 0  and t.user_id = u.invite_userid
						group by u.invite_userid)";
		
		$query = $this->db->query ( $_sql_cpsmoney );
		$ret5 = $query->row_array ();
		$retData ['sum_money'] = $ret5 ['sum_money'];
		
		//TODO 暂无推广会员还款总额
		

		return $retData;
	
	}
	
	function vipRank($data = array()) {
		$sql = "select user.*,rank.vip_name,rank.minscore,rank.maxscore,rank.investpercent,rank.pic  from rsh_user as user left join rsh_invest_rank as rank on  user.invest_rank_id = rank.id where  user.user_id=" . $data ['user_id'] . "";
		$query = $this->db->query ( $sql );
		$ret = $query->row_array ();
		return $ret;
	}
	
	function nextVipRank($val) {
		$sql = "select * from rsh_invest_rank " ;
		if(isset($val))  $sql .= "  where  minscore <=" . $val  . " and maxscore>". $val;
		else  $sql .= " order by id asc limit 1 ";
		$query = $this->db->query ( $sql );
		$ret = $query->row_array ();
		return $ret;
	}
	
	function get_online_usercount(){
		$_sql = "SELECT  count(1) as online_count from rsh_system_admin_user where(unix_timestamp() - login_time ) < 1800";
		$query = $this->db->query($_sql);
		return $query->row();
	}
	


}
